package com.wfh.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DataHelper extends SQLiteOpenHelper {

	private static final String dbname = "wfh";
	private SQLiteDatabase sqlitedatabase = null;
	private final String banners = "CREATE TABLE IF NOT EXISTS banners (banner_id int(11) NOT NULL,shop_id int(11) NOT NULL,img varchar(90) NOT NULL,url varchar(90) NOT NULL,bak varchar(20) NOT NULL,position int(11) NOT NULL,status tinyint(2) NOT NULL, PRIMARY KEY (banner_id))";
	private final String shops = "CREATE TABLE IF NOT EXISTS shops (shop_id int(11) NOT NULL,shop_name varchar(20) NOT NULL,shop_address varchar(70),shop_tel varchar(20),shop_person varchar(10),shop_phone varchar(20),longitude varchar(20),latitude varchar(40),bak varchar(200),PRIMARY KEY (shop_id))";
	private final String versions = "CREATE TABLE IF NOT EXISTS versions (shop_id int(11) NOT NULL,init varchar(10) NOT NULL default '1.00',weeks varchar(10) NOT NULL default '1.00',hots varchar(10) NOT NULL default '1.00',bests varchar(10) NOT NULL default '1.00',promotions varchar(10) NOT NULL default '1.00',products varchar(10) NOT NULL default '1.00', PRIMARY KEY (shop_id))";
	private final String products = "CREATE TABLE IF NOT EXISTS products (product_id int(11) NOT NULL,shop_id int(11) NOT NULL,product_name varchar(10),product_desc varchar(10),product_img varchar(90),product_price varchar(10),product_price_oem varchar(10),position int(11),is_week tinyint(2),is_hot tinyint(2),is_best tinyint(2),is_promotion tinyint(2),promotion_end_time varchar(50),PRIMARY KEY (product_id))";
	private final String tickets = "CREATE TABLE IF NOT EXISTS tickets (juan_id int(11) NOT NULL,shop_id int(11) NOT NULL,real_money varchar(30) NOT NULL,reduce_money varchar(30) NOT NULL,juan_name varchar(50) NOT NULL,exp_date varchar(30) NOT NULL,code varchar(50),status tinyint(2) default 0)";//status 0未用 1使用
	private Context context;

	public DataHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, dbname, factory, version);
		sqlitedatabase = this.getWritableDatabase();
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}

	public void initData() {
		sqlitedatabase.execSQL(banners);// banner
		sqlitedatabase.execSQL(shops);// 分店信息
		sqlitedatabase.execSQL(versions);// 每个action更新信息
		sqlitedatabase.execSQL(products);
		sqlitedatabase.execSQL(tickets);
	}

	/**
	 * 判断某张表是否存在
	 * 
	 * @param tabName
	 *            表名
	 * @return
	 */
	public boolean tabbleIsExist(String tableName) {
		boolean result = false;
		if (tableName == null) {
			return false;
		}
		Cursor cursor = null;
		try {
			String sql = "select count(*) as c from Sqlite_master  where type ='table' and name ='"
					+ tableName.trim() + "' ";
			cursor = sqlitedatabase.rawQuery(sql, null);
			if (cursor.moveToNext()) {
				int count = cursor.getInt(0);
				if (count > 0) {
					result = true;
				}
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		cursor.close();
		return result;
	}

	/**
	 * 查询结果集
	 */
	public Cursor query(String table, String[] columns, String selection,
			String[] selectionArgs, String groupBy, String having,
			String orderBy, String limit) {
		return sqlitedatabase.query(table, columns, selection, selectionArgs,
				groupBy, having, orderBy, limit);

	}

	/**
	 * rawquery结果集
	 */

	public Cursor rawQuery(String sql, String[] selectionArgs) {
		return sqlitedatabase.rawQuery(sql, selectionArgs);
	}

	/**
	 * 插入新记录
	 */

	public long insert(String table, String nullColumnHack, ContentValues values) {
		return sqlitedatabase.insert(table, nullColumnHack, values);
	}

	/**
	 * 删除记录
	 */

	public int delete(String table, String whereClause, String[] whereArgs) {
		return sqlitedatabase.delete(table, whereClause, whereArgs);
	}

	/**
	 * 删除表所有记录
	 */

	public void deleteAll(String table) {
		sqlitedatabase.execSQL("delete from " + table);
	}

	/**
	 * 更新
	 */

	public int update(String table, ContentValues values, String whereClause,
			String[] whereArgs) {
		return sqlitedatabase.update(table, values, whereClause, whereArgs);
	}

	/**
	 * 执行sql语句
	 */

	public void execSQL(String sql) {
		sqlitedatabase.execSQL(sql);
	}

	/**
	 * 获取表数量
	 */

	public int getCount(String tableName) {
		int count;
		Cursor cursor = sqlitedatabase.rawQuery("select * from " + tableName,
				null);
		count = cursor.getCount();
		cursor.close();
		return count;
	}

	// 获取版本号

	public String getVersion(String shop_id, String action) {

		Cursor cursor = sqlitedatabase.rawQuery(
				"select * from versions where shop_id=?",
				new String[] { shop_id });
		if (cursor.getColumnIndex(action) == -1) {
			Log.i("error", "没有该列");
			cursor.close();
			return null;
		}
		
		if (cursor.getCount() != 0){
			cursor.moveToNext();
			String version=cursor.getString(cursor.getColumnIndex(action));
			cursor.close();
			return version;
		}
		return null;
	}

	// 设置版本号

	public void setVersion(String shop_id, String action,String version) {
		sqlitedatabase.execSQL("update versions set "+action+"='"+version+"' where shop_id="+shop_id);
	}
	
	/**
	 * 关闭数据库连接
	 */
	public void close() {
		sqlitedatabase.close();
	}

}
